﻿
Imports System.Text.RegularExpressions
Imports Microsoft.VisualBasic

''' <summary>
''' Function Or Sub Define
''' </summary>
''' <remarks></remarks>
Module ModSys

#Region "Enum Operator"

    ''' <summary>
    ''' Return Enum Item Name is Value
    ''' </summary>
    ''' <param name="EType">Enum Class</param>
    ''' <param name="EnumText">Enum Item Name</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReturnEnumValue(ByVal EType As Type, ByVal EnumText As String) As Integer
        Dim EnumValue As Integer = -1
        Try
            If [Enum].IsDefined(EType, EnumText) = True Then
                EnumValue = CType(([Enum].Parse(EType, EnumText)), Int32)
            End If
        Catch ex As Exception
            MsgBox("ReturnEnumValue:" & ex.ToString & ex.StackTrace)
        End Try
        Return EnumValue
    End Function

    ''' <summary>
    ''' 返回枚舉對應的Case--Sql語句
    ''' </summary>
    ''' <param name="eType">枚舉</param>
    ''' <param name="columnName">列名</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Re_EnumSql(ByVal eType As Type, ByVal columnName As String) As String
        Dim Sql As String = ""
        Try
            Sql &= " Case " & columnName & "  "
            For Each Value As Object In [Enum].GetValues(eType)
                Sql &= "  when " & Value & "  then  '" & Value.ToString & "'  "
            Next
            Sql &= "  Else  ''  end "

        Catch ex As Exception
            MessageBox.Show("Re_EnumSql:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Return Sql
    End Function

    ''' <summary>
    ''' 填充枚舉值
    ''' </summary>
    ''' <param name="cbx">下拉框</param>
    ''' <param name="EType">枚舉類型</param>
    ''' <param name="selectIndex">默認選擇項</param>
    ''' <remarks></remarks>
    Public Sub FillComboxForEnum(ByRef cbx As ComboBox, ByVal EType As Type, Optional ByVal selectIndex As Integer = -1)
        Try
            cbx.Items.Clear()
            For Each Value As Object In [Enum].GetNames(EType)
                cbx.Items.Add(Value)
            Next
            If selectIndex <> -1 AndAlso cbx.Items.Count > selectIndex Then
                cbx.SelectedIndex = selectIndex
            End If
        Catch ex As Exception
            MessageBox.Show("FillComboxForEnum:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

#Region "Text Operator"

    Public Function isNull(ByVal strInput As String) As Boolean
        Return String.IsNullOrEmpty(strInput)
    End Function

    ''' <summary>
    ''' Replace Unlawful Char
    ''' </summary>
    ''' <param name="TextValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Replace_Text(ByVal TextValue As String) As String
        Try
            TextValue = TextValue.Replace("'", "")
            TextValue = TextValue.Replace("[", "")
            TextValue = TextValue.Replace(",", "")
        Catch ex As Exception
            MsgBox("Replace_Text:" & ex.ToString & ex.StackTrace)
        End Try
        Return TextValue
    End Function

    ''' <summary>
    ''' 去掉某些SQL中不能使用的字符串
    ''' </summary>
    ''' <param name="inputText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function safeText(ByVal inputText As String, Optional ByVal ifTrim As Boolean = True) As String
        If ifTrim = True Then
            Return inputText.Trim.Replace("'", "")
        Else
            Return inputText.Replace("'", "")
        End If
    End Function
    Public Function safeText(ByVal inputTextBox As TextBox, Optional ByVal ifTrim As Boolean = True) As String
        If ifTrim = True Then
            Return inputTextBox.Text.Trim.Replace("'", "")
        Else
            Return inputTextBox.Text.Replace("'", "")
        End If
    End Function

    Public Function TextLen(ByVal inputTextBox As TextBox, Optional ByVal ifTrim As Boolean = True) As String
        Return TextLen(inputTextBox.Text, ifTrim)
    End Function
    Public Function TextLen(ByVal inputText As String, Optional ByVal ifTrim As Boolean = True) As String
        If ifTrim = True Then
            Return inputText.Trim.Length
        Else
            Return inputText.Length
        End If
    End Function
    ''' <summary>
    ''' 驗證輸入的字符是否為數字
    ''' </summary>
    ''' <param name="souStr">輸入的字符串</param>
    ''' <param name="Patter">那種驗證公式</param>
    ''' <param name="Length">小數的位數</param>
    ''' <returns>
    ''' <list>True 驗證通過</list>
    ''' <list>驗證未通過</list>
    ''' </returns>
    ''' <remarks>
    ''' <list>Create Person:Huyang</list>
    ''' <list>Create Date:2007-04-06</list>
    ''' <list>缺陷：第一次輸入-號不能輸入，只有存在數值時才能輸入-</list>
    ''' </remarks>
    Public Function ValidatedIsNumeric(ByVal souStr As String, Optional ByVal Patter As Integer = 2, Optional ByVal Length As Integer = 2) As Boolean
        Dim Bool As Boolean = False
        Try

            Dim ValiString As String = ""


            Select Case Patter
                Case 0

                Case 1

                Case 2
                    If Length = 0 Then
                        ValiString = "^(-|\+)?\d+$"
                    Else
                        ValiString = "^-?\d+(\.\d{0," & Length & "})?$"
                    End If
                Case Else

            End Select

            Dim Reg As New Regex(ValiString)

            If Reg.IsMatch(souStr) = True AndAlso System.Text.Encoding.Default.GetByteCount(souStr) = souStr.Length Then
                Bool = True
            End If

        Catch ex As Exception
            MsgBox("ValidatedIsNumeric:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkCancel, Msg.CompanyName)
        End Try
        Return Bool
    End Function

    Public Enum LanguageTo
        SimplifiedChinese = VbStrConv.SimplifiedChinese
        TraditionnalChinese = VbStrConv.TraditionalChinese
    End Enum
    ''' <summary>
    ''' 語言轉化方法（可以將語言轉化成繁體中文或簡體中文）
    ''' </summary>
    ''' <param name="inputStr">輸入的字符串</param>
    ''' <param name="TargetLanguage">要轉化的目標語言</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function StrConv(ByVal inputStr As String, ByVal TargetLanguage As LanguageTo) As String
        Dim str As String = String.Empty
        str = Strings.StrConv(inputStr, TargetLanguage, 1033) '使用1033作為LocalID，可以處理所有的非Assic字符
        Return str
    End Function

#End Region

#Region "DataGridView Operator"

    ''' <summary>
    ''' 為網格綁定標頭
    ''' </summary>
    ''' <param name="DGView">網格</param>
    ''' <param name="Hash">網格標頭Hash表</param>
    ''' <remarks></remarks>
    Public Sub BindDataGridViewCaption(ByRef DGView As DataGridView, ByVal Hash As Hashtable)
        Try
            If TypeOf DGView.DataSource Is DataTable Then
                Dim Dtab As DataTable = DirectCast(DGView.DataSource, DataTable)
                For Each Column As DataColumn In Dtab.Columns
                    If Hash.ContainsKey(Column.ColumnName) = True Then
                        Column.Caption = Hash(Column.ColumnName)
                        DGView.Columns(Column.ColumnName).HeaderText = Hash(Column.ColumnName)
                    Else
                        DGView.Columns(Column.ColumnName).Visible = False
                    End If
                Next
            End If
        Catch ex As Exception
            MsgBox("BindDataGridViewCaption:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 為網格綁定標頭
    ''' </summary>
    ''' <param name="DGView">網格</param>
    ''' <param name="Hash">網格標頭Hash表</param>
    ''' <remarks></remarks>
    Public Sub BindDataGridViewCaption_1(ByRef DGView As DataGridView, ByVal Hash As Hashtable)
        Try
            If TypeOf DGView.DataSource Is DataTable Then
                Dim Dtab As DataTable = DirectCast(DGView.DataSource, DataTable)
                For Each Column As DataColumn In Dtab.Columns
                    If Hash.ContainsKey(Column.ColumnName) = True Then
                        Column.Caption = Hash(Column.ColumnName)
                        DGView.Columns(Column.ColumnName).HeaderText = Hash(Column.ColumnName)
                    End If
                Next
            End If
        Catch ex As Exception
            MsgBox("BindDataGridViewCaption:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 設置網格那些列可以編輯
    ''' </summary>
    ''' <param name="DGView">網格</param>
    ''' <param name="Hash">列名集合</param>
    ''' <remarks></remarks>
    Public Sub SetDataGridViewEnabledColumn(ByRef DGView As DataGridView, ByVal Hash As Hashtable)
        Try
            For Each Column As DataGridViewColumn In DGView.Columns
                Dim ColumnName As String = Column.Name
                If Hash.ContainsKey(ColumnName) = True Then
                    Column.ReadOnly = False
                Else
                    Column.ReadOnly = True
                End If
            Next
        Catch ex As Exception
            MsgBox("SetDataGridViewEnabledColumn:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 設置網格那些列可以編輯
    ''' </summary>
    ''' <param name="DGView">網格</param>
    ''' <param name="Hash">列名集合</param>
    ''' <remarks></remarks>
    Public Sub SetDataGridViewEnabledColumn(ByRef DGView As DataGridView, ByVal Hash As Hashtable, ByVal bReadOnly As Boolean)
        Try
            For Each Column As DataGridViewColumn In DGView.Columns
                Dim ColumnName As String = Column.Name
                If Hash.ContainsKey(ColumnName) = True Then
                    Column.ReadOnly = bReadOnly
                Else
                    Column.ReadOnly = True
                End If
            Next
        Catch ex As Exception
            MsgBox("SetDataGridViewEnabledColumn:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' 格式化網格樣式
    ''' </summary>
    ''' <param name="DGView">網格</param>
    ''' <param name="addBool">新增</param>
    ''' <param name="deleteBool">刪除</param>
    ''' <param name="autoSizeBool">自動大小</param>
    ''' <remarks></remarks>
    Public Sub FullFormatDataGridView(ByRef DGView As DataGridView, Optional ByVal addBool As Boolean = False, Optional ByVal deleteBool As Boolean = False, _
    Optional ByVal editBool As Boolean = False, Optional ByVal autoSizeBool As Boolean = True, Optional ByVal RowHeadersVisible As Boolean = True)
        Try
            With DGView
                .AllowUserToAddRows = addBool
                .AllowUserToDeleteRows = deleteBool
                .AutoSize = True
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = True
                .AllowUserToOrderColumns = True
                '.AutoResizeColumns()
                .GridColor = Color.Gainsboro
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter
                '.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells
                .CellBorderStyle = DataGridViewCellBorderStyle.Single
                .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
                .ReadOnly = Not (addBool OrElse editBool OrElse deleteBool)
                If addBool = True OrElse editBool = True Then
                    .SelectionMode = DataGridViewSelectionMode.RowHeaderSelect
                Else
                    .SelectionMode = DataGridViewSelectionMode.FullRowSelect
                End If
                .MultiSelect = False
                .RowHeadersVisible = RowHeadersVisible

                'If autoSizeBool = True Then
                '    ''多行顯示
                '    DGView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                '    DGView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders
                '    DGView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                '    DGView.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True
                '    DGView.Update()
                'End If
                '.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(221, 221, 221)

                '.AutoResizeColumns()

            End With
        Catch ex As Exception
            MsgBox("FormatDataGridView:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub

    ''' <summary>
    ''' set DataGridView Row BackColor
    ''' </summary>
    ''' <param name="URow"></param>
    ''' <param name="ColumnName"></param>
    ''' <remarks></remarks>
    Public Sub SetDataGridViewRowBackColor(ByRef URow As DataGridViewRow, ByVal ColumnName As String)
        Try
            If URow Is Nothing Then Exit Sub
            Dim CellValue As String = URow.Cells(ColumnName).Value.ToString.Trim & ""

            Dim fStatus As E_VouStatusCode = ReturnEnumValue(GetType(E_VouStatusCode), CellValue)

            Select Case fStatus

                Case E_VouStatusCode.A ''保存

                Case E_VouStatusCode.B ''核准

                    URow.DefaultCellStyle.BackColor = System.Drawing.SystemColors.Info

                Case E_VouStatusCode.C ''反核准

                    URow.DefaultCellStyle.BackColor = Color.DeepSkyBlue

                Case E_VouStatusCode.D ''取消

                Case E_VouStatusCode.E ''作廢

                Case E_VouStatusCode.F ''結案

                    URow.DefaultCellStyle.BackColor = Color.Gainsboro

                Case Else

            End Select
        Catch ex As Exception
            MessageBox.Show("SetDataGridViewRowBackColor:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' Set DataGridView BackColor
    ''' </summary>
    ''' <param name="DGView">DataGridView</param>
    ''' <param name="ColumnName">ColumnName</param>
    ''' <remarks></remarks>
    Public Sub SetDataGridViewBackColor(ByRef DGView As DataGridView, ByVal ColumnName As String)
        Try
            If DGView Is Nothing Then Exit Sub
            If DGView.Columns.Contains(ColumnName) = False Then Exit Sub

            Dim Dtab As DataTable = DirectCast(DGView.DataSource, DataTable)
            If Dtab.Rows.Count <= 0 Then Exit Sub
            If DGView.RowCount <> Dtab.Rows.Count Then Exit Sub

            For I As Integer = 0 To Dtab.Rows.Count - 1

                Dim CellValue As String = DGView.Rows(I).Cells(ColumnName).Value & ""

                Dim fStatus As E_VouStatusCode = ReturnEnumValue(GetType(E_VouStatus), CellValue)

                Select Case fStatus

                    Case E_VouStatusCode.A ''保存

                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.AliceBlue

                    Case E_VouStatusCode.B ''核准

                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.LightYellow

                    Case E_VouStatusCode.C ''反核准

                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.DeepSkyBlue

                    Case E_VouStatusCode.D ''取消
                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.White

                    Case E_VouStatusCode.E ''作廢

                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.DarkRed

                    Case E_VouStatusCode.F ''結案

                        DGView.Rows(I).DefaultCellStyle.BackColor = Color.Gainsboro

                    Case Else

                End Select

            Next
        Catch ex As Exception
            MessageBox.Show("SetDataGridViewBackColor:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' SetRowHeadersWidth
    ''' </summary>
    ''' <param name="DGView">DataGridView</param>
    ''' <param name="SortCol">Sort Column Name</param>
    ''' <remarks></remarks>
    Public Sub SetRowHeadersWidth(ByRef DGView As DataGridView, ByVal SortCol As String)
        Try

        Catch ex As Exception
            MessageBox.Show("SetRowHeadersWidth:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' Select DataGridView Row
    ''' </summary>
    ''' <param name="DGView">DataGridView</param>
    ''' <param name="ColKey">Column Key</param>
    ''' <param name="ColValue">Column Value</param>
    ''' <remarks></remarks>
    Public Sub SelectDataGridViewRow(ByRef DGView As DataGridView, ByVal ColKey As String, ByVal ColValue As String)
        Try
            For I As Integer = 0 To DGView.RowCount - 1
                If DGView.Rows(I).Cells(ColKey).Value.ToString.ToLower = ColValue.ToLower Then
                    DGView.CurrentCell = DGView.Rows(I).Cells(ColKey)
                    Exit For
                End If
            Next
        Catch ex As Exception
            MessageBox.Show("SelectDataGridViewRow:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub DataGridViewSortModeEnabled(ByRef DGView As DataGridView)

        For I As Integer = 0 To DGView.ColumnCount - 1
            DGView.Columns(I).SortMode = DataGridViewColumnSortMode.NotSortable
        Next

    End Sub

    ''' <summary>
    ''' 向一個DataGridView中添加數據
    ''' </summary>
    ''' <param name="dgv">要添加到的DataGridView</param>
    ''' <param name="table">需要添加的數據</param>
    ''' <remarks></remarks>
    Public Sub InsertToDGV(ByRef dgv As DataGridView, ByVal table As DataTable)
        Dim dt As DataTable = CType(dgv.DataSource, DataTable)
        If Not dt Is Nothing Then
            For Each drow As DataRow In table.Rows
                Dim nrow As DataRow = dt.NewRow()
                For Each col As DataColumn In table.Columns
                    If dt.Columns.Contains(col.ColumnName) Then
                        nrow(col.ColumnName) = drow(col.ColumnName)
                    End If
                Next
                dt.Rows.Add(nrow)
            Next
            dt.AcceptChanges()
            dgv.DataSource = dt
        End If
    End Sub
    ''' <summary>
    ''' 向一個DataGridView中添加數據
    ''' </summary>
    ''' <param name="dgv">要添加到的DataGridView</param>
    ''' <param name="table">需要添加的數據</param>
    ''' <param name="KeyField">主鍵列</param>
    ''' <remarks></remarks>
    Public Sub UpdateDGV(ByRef dgv As DataGridView, ByVal table As DataTable, ByVal KeyField As String)
        Dim dt As DataTable = CType(dgv.DataSource, DataTable)
        If Not dt Is Nothing Then
            For Each drow As DataRow In table.Rows
                Dim nrow() As DataRow = dt.Select(KeyField + "='" + drow(KeyField).ToString + "'")
                If nrow.Length = 1 Then
                    For Each col As DataColumn In table.Columns
                        If dt.Columns.Contains(col.ColumnName) Then
                            nrow(0)(col.ColumnName) = drow(col.ColumnName)
                        End If
                    Next
                ElseIf nrow.Length > 1 Then
                    Msg.MsgError("主鍵錯誤：" + KeyField + " 不能唯一標示數據項")
                    Exit Sub
                End If
            Next
            dt.AcceptChanges()
            dgv.DataSource = dt
        End If
    End Sub
#End Region

#Region "DataSet or DataTable"

#Region "數據合並"

    ''' <summary>
    ''' 合並數據
    ''' </summary>
    ''' <param name="Dtab">合並的數據表</param>
    ''' <param name="sumArr">匯總列集合</param>
    ''' <param name="sortArr">排序列集合(那些列相同時數據合並)</param>
    ''' <returns>數據表</returns>
    ''' <remarks>
    ''' <list>Create Person:Huyang</list>
    ''' <list>Create Date:2007-04-13</list>
    ''' </remarks>
    Public Function UnionDataGridViewRowData(ByVal Dtab As DataTable, ByVal sumArr As ArrayList, ByVal sortArr As ArrayList) As DataTable
        Try
            If sumArr.Count <= 0 OrElse sumArr.Count <= 0 Then GoTo A

            Dim sortExp As String = ""
            Dim TextValue As New System.Text.StringBuilder
            For Each Col As String In sortArr
                If TextValue.ToString.Trim <> "" Then
                    TextValue.Append(",")
                End If
                TextValue.Append(Col)
            Next
            sortExp = TextValue.ToString

B:
            Dim DRow() As DataRow = Dtab.Select("", sortExp)
            For I As Integer = 0 To DRow.Length - 1
                Dim OldRow As DataRow = DRow(I)
                If OldRow.RowState <> DataRowState.Deleted Then
                    For J As Integer = I + 1 To DRow.Length - 1
                        Dim NewRow As DataRow = DRow(J)
                        If NewRow.RowState <> DataRowState.Deleted Then
                            Dim Bool As Boolean = False
                            For Each ColumnName As String In sortArr
                                If OldRow(ColumnName).ToString.Trim.ToLower <> NewRow(ColumnName).ToString.Trim.ToLower Then
                                    Bool = True
                                    Exit For
                                End If
                            Next
                            If Bool = False Then
                                For Each ColumnName As String In sumArr
                                    OldRow(ColumnName) = Val(OldRow(ColumnName) & "") + Val(NewRow(ColumnName) & "")
                                Next
                                NewRow.Delete()
                                'Dtab.AcceptChanges()
                                'GoTo B
                            End If
                        End If
                    Next
                End If
            Next
            Dtab.AcceptChanges()

        Catch ex As Exception
            MsgBox("UnionDataGridViewRowData:" & ex.Message & ex.Message)
        End Try
A:
        Return Dtab
    End Function

    ''' <summary>
    ''' 合並數據(數據須要處理孖表)
    ''' </summary>
    ''' <param name="Dtab">合並的數據表</param>
    ''' <param name="sumArr">匯總列集合</param>
    ''' <param name="sortArr">排序列集合(那些列相同時數據合並)</param>
    ''' <param name="DtabDetail">從表</param>
    ''' <param name="KeyCol">主表關聯從表的列(從表中的列)</param>
    ''' <param name="PerKeyCol">主表關聯從表的列(主表中的列)</param>
    ''' <returns>數據表</returns>
    ''' <remarks>
    ''' <list>Create Person:Huyang</list>
    ''' <list>Create Date:2007-04-13</list>
    ''' </remarks>
    Public Function UnionDataGridViewRowData(ByVal Dtab As DataTable, ByVal sumArr As ArrayList, ByVal sortArr As ArrayList, ByRef DtabDetail As DataTable, ByVal PerKeyCol As String, ByVal KeyCol As String) As DataTable
        Try
            If sumArr.Count <= 0 OrElse sumArr.Count <= 0 Then GoTo A

            Dim sortExp As String = ""
            Dim TextValue As New System.Text.StringBuilder
            For Each Col As String In sortArr
                If TextValue.ToString.Trim <> "" Then
                    TextValue.Append(",")
                End If
                TextValue.Append(Col)
            Next
            sortExp = TextValue.ToString

B:
            Dim DRow() As DataRow = Dtab.Select("", sortExp)
            For I As Integer = 0 To DRow.Length - 1
                Dim OldRow As DataRow = DRow(I)
                If OldRow.RowState <> DataRowState.Deleted Then
                    For J As Integer = I + 1 To DRow.Length - 1
                        Dim NewRow As DataRow = DRow(J)
                        If NewRow.RowState <> DataRowState.Deleted Then
                            Dim Bool As Boolean = False
                            For Each ColumnName As String In sortArr
                                If OldRow(ColumnName).ToString.Trim.ToLower <> NewRow(ColumnName).ToString.Trim.ToLower Then
                                    Bool = True
                                    Exit For
                                End If
                            Next
                            If Bool = False Then
                                For Each ColumnName As String In sumArr
                                    OldRow(ColumnName) = Val(OldRow(ColumnName) & "") + Val(NewRow(ColumnName) & "")
                                Next
                                ''處理從表
                                If DtabDetail.Rows.Count > 0 Then
                                    Dim KeyA As Object = NewRow(PerKeyCol)
                                    Dim KeyB As Object = OldRow(PerKeyCol)
                                    Dim filter As String = KeyCol & "='" & KeyA & "'"
                                    Dim DRowDetail() As DataRow = DtabDetail.Select(filter)
                                    If DRowDetail.Length > 0 Then
                                        For Each Row As DataRow In DRowDetail
                                            Row(KeyCol) = KeyB
                                        Next
                                        DtabDetail.AcceptChanges()
                                    End If
                                End If
                                NewRow.Delete()
                                'Dtab.AcceptChanges()
                                'GoTo B
                            End If
                        End If
                    Next
                End If
            Next
            Dtab.AcceptChanges()

        Catch ex As Exception
            MsgBox("UnionDataGridViewRowData:" & ex.Message & ex.Message)
        End Try
A:
        Return Dtab
    End Function



    ''' <summary>
    ''' 判斷連個表的列是否一致
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="dt2"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function isColumnsEquals(ByVal dt As DataTable, ByVal dt2 As DataTable) As Boolean
        Try
            Dim succed As Boolean = True
            For Each col As DataColumn In dt.Columns
                If Not dt2.Columns.Contains(col.ColumnName) Then
                    succed = False
                End If
            Next
            For Each col As DataColumn In dt2.Columns
                If Not dt.Columns.Contains(col.ColumnName) Then
                    succed = False
                End If
            Next
            Return succed
        Catch ex As Exception
            Msg.MsgError("ModSys.CS—isColumnsEquals:" + ex.Message)
        End Try
    End Function

#End Region

#Region "刪除空行"

    ''' <summary>
    ''' 刪除空行
    ''' </summary>
    ''' <param name="Dtab">表</param>
    ''' <param name="Arr">列</param>
    ''' <remarks>
    ''' <list>Create By:Huyang</list>
    ''' <list>Create Date:2007-04-23</list>
    ''' </remarks>
    Public Sub DeleteNewRowForDtab(ByRef Dtab As DataTable, ByVal Arr As ArrayList)
        Try
            If Arr.Count <= 0 Then Exit Sub

            Dim Filter As String = ""
            Dim TextValue As New System.Text.StringBuilder
            For Each ColumnName As String In Arr
                If TextValue.ToString.Trim <> "" Then
                    TextValue.Append("  and  ")
                End If
                TextValue.Append(" Isnull(" & ColumnName & ",'')='' ")
            Next
            Filter = TextValue.ToString
            Dim DRow() As DataRow = Dtab.Select(Filter)
            For Each Row As DataRow In DRow
                Row.Delete()
            Next
            Dtab.AcceptChanges()

        Catch ex As Exception
            MessageBox.Show("DeleteNewRowForDtab:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

#Region "添加行號"

    ''' <summary>
    ''' 添加行號
    ''' </summary>
    ''' <param name="Dtab">數據表</param>
    ''' <param name="RowId">對應列</param>
    ''' <remarks></remarks>
    Public Sub AddRowIdForDataTable(ByRef Dtab As DataTable, ByVal RowId As String)
        Try
            If Dtab.Columns.Contains(RowId) = True Then
                With Dtab.Columns(RowId)
                    For I As Integer = 0 To Dtab.Rows.Count - 1
                        Dtab.Rows(I).Item(RowId) = I + 1
                    Next
                End With
            End If
            Dtab.AcceptChanges()
        Catch ex As Exception
            MessageBox.Show("AddRowIdForDataTable:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' 添加行號
    ''' </summary>
    ''' <param name="Dset"></param>
    ''' <param name="RowId"></param>
    ''' <remarks></remarks>
    Public Sub AddRowIdForDset(ByRef Dset As DataSet, ByVal RowId As String)
        Try
            For Each Dtab As DataTable In Dset.Tables
                AddRowIdForDataTable(Dtab, RowId)
            Next
            Dset.AcceptChanges()
        Catch ex As Exception
            MessageBox.Show("AddRowIdForDset:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

#Region "移除隱蔽列"

    Public Sub RemoveHideColumn(ByVal Grid As DataGridView, ByRef Dtab As DataTable)
        Try
            For Each Column As DataGridViewColumn In Grid.Columns
                If Column.Visible = False Then
                    If Dtab.Columns.Contains(Column.Name) = True Then
                        Dtab.Columns.Remove(Column.Name)
                    End If
                End If
            Next
            Dtab.AcceptChanges()
        Catch ex As Exception
            MessageBox.Show("RemoveHideColumn:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

#Region "執行Sql語句"

    ''' <summary>
    ''' 執行Sql語句
    ''' </summary>
    ''' <param name="Sql"></param>
    ''' <param name="EType"></param>
    ''' <param name="TranBool"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Exec(ByVal Sql As String, Optional ByVal EType As E_ExecType = E_ExecType.DataSet, Optional ByVal TranBool As Boolean = False) As Object
        Dim sqlcn As SqlClient.SqlConnection
        Dim Tran As SqlClient.SqlTransaction
        Try
            sqlcn = App.GetConn()

            If TranBool = True Then
                Tran = sqlcn.BeginTransaction
            End If
            Select Case EType

                Case E_ExecType.DataSet

                    Dim Dset As New DataSet
                    Dim mFactory As New SqlClient.SqlDataAdapter(Sql, sqlcn)
                    mFactory.Fill(Dset)
                    Return Dset

                Case E_ExecType.ExecuteNonQuery

                    Dim I As Integer = 0

                    Dim sqlcmd As New SqlClient.SqlCommand
                    With sqlcmd
                        .Connection = sqlcn
                        .CommandText = Sql
                        .CommandType = CommandType.Text
                        If TranBool = True Then
                            .Transaction = Tran
                        End If
                        I = .ExecuteNonQuery()
                    End With

                    If TranBool = True Then
                        Tran.Commit()
                    End If

                    Return I

                Case E_ExecType.ExecuteScalar

                    Dim Value As Object
                    Dim sqlcmd As New SqlClient.SqlCommand
                    With sqlcmd
                        .Connection = sqlcn
                        .CommandText = Sql
                        .CommandType = CommandType.Text
                        If TranBool = True Then
                            .Transaction = Tran
                        End If
                        Value = .ExecuteScalar
                    End With

                    If TranBool = True Then
                        Tran.Commit()
                    End If

                    Return Value

                Case E_ExecType.ExecuteReader



                Case E_ExecType.DataTable

                    Dim Dtab As New DataTable
                    Dim mFactory As New SqlClient.SqlDataAdapter(Sql, sqlcn)
                    mFactory.Fill(Dtab)
                    Return Dtab

            End Select
            If sqlcn.State <> ConnectionState.Closed Then
                sqlcn.Close()
            End If
        Catch ex As Exception
            If TranBool = True Then
                Tran.Rollback()
            End If

            MessageBox.Show("對不起,讀取數據出錯!", Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Function

#End Region

#Region "设置datatable的caption属性"
    ''' <summary>
    ''' 為網格綁定標頭    ''' </summary>
    ''' <param name="DTab">DataTable</param>
    ''' <param name="Hash">DataTable標頭Hash表</param>
    ''' <remarks></remarks>
    Public Sub BindDataTableCaption(ByRef DTab As DataTable, ByVal Hash As Hashtable)
        Try
            For Each Column As DataColumn In DTab.Columns
                If Hash.ContainsKey(Column.ColumnName) = True Then
                    Column.Caption = Hash(Column.ColumnName)
                Else
                    DTab.Columns.Remove(Column.ColumnName)
                End If
                DTab.AcceptChanges()
            Next
        Catch ex As Exception
            MsgBox("BindDataTableCaption:" & ex.Message & ex.StackTrace, MsgBoxStyle.Critical And MsgBoxStyle.OkOnly, Msg.CompanyName)
        End Try
    End Sub
#End Region

#End Region

#Region "TreeView Methods"

    ''' <summary>
    ''' 加載樹
    ''' </summary>
    ''' <param name="Tvw">樹</param>
    ''' <param name="TextTitle">首結點</param>
    ''' <param name="CheckBool">是否出現複選框</param>
    ''' <remarks></remarks>
    Public Sub Load_TreeView(ByRef Tvw As TreeView, ByVal TextTitle As String, Optional ByVal CheckBool As Boolean = False, Optional ByVal ExpandBool As Boolean = True)
        Try
            Tvw.Nodes.Clear()
            Dim Node As TreeNode = Tvw.Nodes.Add("0", TextTitle)
            Node.Tag = "0"
            If CheckBool = True Then
                Tvw.CheckBoxes = True
            End If
            LoadNodeImage(Node, 0)
            Node.Nodes.Add("...")
            Tvw.SelectedNode = Node
            If ExpandBool = True Then
                Node.Expand()
            End If
        Catch ex As Exception
            MessageBox.Show("Load_TreeView:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' 加載圖片
    ''' </summary>
    ''' <param name="Node"></param>
    ''' <param name="Level"></param>
    ''' <remarks></remarks>
    Public Sub LoadNodeImage(ByRef Node As TreeNode, ByVal Level As Integer)
        Select Case Level
            Case 0
                Node.ImageIndex = 1
                Node.SelectedImageIndex = 2
            Case 1
                Node.ImageIndex = 1
                Node.SelectedImageIndex = 2
            Case 2
                Node.ImageIndex = 1
                Node.SelectedImageIndex = 2
            Case 3
                Node.ImageIndex = 1
                Node.SelectedImageIndex = 2
            Case 4
                Node.ImageIndex = 1
                Node.SelectedImageIndex = 2
            Case Else
                Node.ImageIndex = 0
                Node.SelectedImageIndex = 1
        End Select
    End Sub

    ''' <summary>
    ''' 結點選擇遞歸
    ''' </summary>
    ''' <param name="Node">結點</param>
    ''' <param name="CheckBool">選擇</param>
    ''' <remarks></remarks>
    Public Sub CheckChildNode(ByRef Node As TreeNode, ByVal CheckBool As Boolean)
        Try
            If Node Is Nothing Then Exit Sub
            Node.Checked = CheckBool
            For Each Tnode As TreeNode In Node.Nodes
                CheckChildNode(Tnode, CheckBool)
            Next
        Catch ex As Exception
            MessageBox.Show("CheckChildNode:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' 選擇父結點
    ''' </summary>
    ''' <param name="Node"></param>
    ''' <remarks></remarks>
    Public Sub CheckParentNode(ByRef Node As TreeNode)
        Try
            If Node.Level = 0 Then Exit Sub

            Dim CheckBool As Boolean = Node.Checked
            Dim ParentNode As TreeNode = Node.Parent
            If ParentNode Is Nothing Then Exit Sub

            Dim I As Integer = 0

            For Each Tnode As TreeNode In ParentNode.Nodes
                If Tnode.Checked = False Then
                    Exit For
                Else
                    I += 1
                End If
            Next

            If ParentNode.Nodes.Count = I Then
                ParentNode.Checked = True
            Else
                ParentNode.Checked = False
            End If

            CheckParentNode(ParentNode)

        Catch ex As Exception
            MessageBox.Show("CheckParentNode:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    ''' <summary>
    ''' 通過Tag得到節點
    ''' </summary>
    ''' <param name="Tvw"></param>
    ''' <param name="Tag"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNodeByTag(ByVal Tvw As TreeView, ByVal Tag As String) As TreeNode
        Dim SelectNode As TreeNode = Nothing
        Try
            Dim SelectBool As Boolean = False
            For Each Tnode As TreeNode In Tvw.Nodes
                Recursion_TreeNode(Tnode, Tag, SelectBool, SelectNode)
                If SelectBool = True Then
                    Exit For
                End If
            Next
        Catch ex As Exception
            MessageBox.Show("GetNodeByTag:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Return SelectNode
    End Function

    ''' <summary>
    ''' 遞迴查找
    ''' </summary>
    ''' <param name="Node">結點</param>
    ''' <param name="Tag">Tag</param>
    ''' <remarks></remarks>
    Public Sub Recursion_TreeNode(ByVal Node As TreeNode, ByVal Tag As String, ByRef SelectBool As Boolean, ByRef SelectNode As TreeNode)
        Try
            If Node Is Nothing Then Exit Sub
            If Node.Tag.ToString.Trim.ToLower = Tag.Trim.ToLower Then
                SelectNode = Node
                SelectBool = True
                Exit Sub
            Else
                If Node.Nodes.Count > 0 Then
                    For Each Tnode As TreeNode In Node.Nodes
                        Recursion_TreeNode(Tnode, Tag, SelectBool, SelectNode)
                    Next
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub



#End Region

#Region "庫位調整過程"

    ''' <summary>
    ''' 庫位調整時查詢當前貨品資訊
    ''' </summary>
    ''' <param name="WhereExp">條件</param>
    ''' <param name="WhereExpB">條件</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReturnMoveDetailFindSql(ByVal WhereExp As String, Optional ByVal WhereExpB As String = "") As String

        Dim Sql As String = ""

        ''當前庫存=本期入-本期出+(期初入-期初出)

        ''庫存表
        Dim Stor_Trade As String = System.Guid.NewGuid().ToString
        ''歷史庫存
        Dim stor_storagehistory As String = System.Guid.NewGuid().ToString

        Dim TableMaster As String = System.Guid.NewGuid().ToString

        Dim Cnname As String = "(select top 1 Cnname from dbo.BSC_Goods b  where a.GoodsBarCode=b.BarCode ) as Cnname"
        Dim SupperItem As String = "(select SupperItem from BSC_Goods b where a.GoodsBarCode=b.BarCode) as SupperItem"
        Dim GoodsBarCode As String = "Replace(GoodsBarCode,'*','') as GoodsBarCode"

        Dim Goodsstatus As String = Re_EnumSql(GetType(E_GoodsStatus), "Goodsstatus")

        Sql &= " select Case a.TradeType when '+' then a.ToStore  "
        Sql &= " when '-' then a.FromStore   "
        Sql &= " else ''  end  as ToStore,  " & vbCrLf
        Sql &= " b.Storeplace,b.Batch,b.GoodsBarCode, b.Goodsstatus,  " & vbCrLf
        Sql &= " case a.TradeType  when '+' then b.Goodsamount  "
        Sql &= " when '-' then (-1)*(b.Goodsamount)   "
        Sql &= " else 0   end as Goodsamount,b.GoodsPrice  " & vbCrLf
        Sql &= " into [#" & Stor_Trade & "] from Stor_TradeMaster a  " & vbCrLf
        Sql &= " inner join Stor_TradeDetail b on (a.BoNum=b.BoNum)  " & vbCrLf
        Sql &= " where Convert(varchar(10),a.OperateDate,21)>=dbo.ReturnCurrentTerm('store','" & PubCompanyCode & "') and Convert(varchar(10),a.OperateDate,21)<=Convert(varchar(10),Getdate(),21)  and Isnull(a.BoStatus,'')='B'" & vbCrLf

        Sql &= " select Convert(Bit,0) as CheckA, ToStore,Storeplace,Batch," & GoodsBarCode & ",c.SupperItem,c.Cnname," & Goodsstatus & "   as  Goodsstatus,Sum(Goodsamount) as Goodsamount,Max(GoodsPrice) as GoodsPrice" & vbCrLf
        Sql &= " into[#" & TableMaster & "] from [#" & Stor_Trade & "] a " & vbCrLf
        Sql &= "  inner join BSC_Goods c on (a.GoodsBarCode=c.barcode) "
        Sql &= " where Isnull(Storeplace,'')<>''  " & WhereExp & WhereExpB & vbCrLf
        Sql &= " group by ToStore,Storeplace,Batch,GoodsBarCode,Goodsstatus,SupperItem,Cnname" & vbCrLf
        Sql &= " having Isnull(Sum(Goodsamount),0)>0" & vbCrLf

        Sql &= " select storeHouse,place,Batch,Barcode,Isnull((Isnull(sum(Beginqty),0)-Isnull(sum(OutQty),0)),0) as Goodsfamount" & vbCrLf
        Sql &= " into [#" & stor_storagehistory & "] from stor_storagehistory a inner join  BSC_SystemProfile b on 'Period_'+a.storehouse=b.FKey" & vbCrLf
        Sql &= " where a.storehouse='" & PubCompanyCode & "' and FCategory='Store'" & vbCrLf
        Sql &= " group by storeHouse,place,Batch,Barcode" & vbCrLf

        Sql &= " update [#" & TableMaster & "]  set Goodsamount=Goodsamount+b.Goodsfamount" & vbCrLf
        Sql &= " from   [#" & TableMaster & "]  a inner join  [#" & stor_storagehistory & "] b  on (ToStore=b.storeHouse and storeplace=b.place and a.Batch=b.Batch and GoodsBarCode=b.Barcode)" & vbCrLf

        Sql &= " select * from [#" & TableMaster & "] a order by Storeplace " & vbCrLf

        Sql &= " drop table [#" & Stor_Trade & "]" & vbCrLf
        Sql &= " drop table [#" & stor_storagehistory & "]" & vbCrLf
        Sql &= " drop table [#" & TableMaster & "]" & vbCrLf

        Return Sql
    End Function

    ''' <summary>
    ''' 倉庫是否控制到倉位
    ''' </summary>
    ''' <param name="fCode">倉庫編號</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReturnIsSetStorageBool(ByVal fCode As String) As Boolean
        Dim fStorageBool As Boolean = False

        Dim Sql As String = " select * from dbo.BSC_StoreHouse where fCode='" & fCode & "' and Isnull(fStorePlace,0)=1  " & vbCrLf

        Dim Dtab As New DataTable
        Dtab = App.ReturnDataTable(Sql)
        If Dtab.Rows.Count > 0 Then
            fStorageBool = True
        End If

        Return fStorageBool
    End Function

#End Region

#Region "狀態控制"

    ''' <summary>
    ''' 設置控件是否可用
    ''' </summary>
    ''' <param name="Pans"></param>
    ''' <param name="bEnabled"></param>
    ''' <remarks></remarks>
    Public Sub setControlsbEnabled(ByVal Pans() As Control, ByVal bEnabled As Boolean)
        Try
            If Pans.Length <= 0 Then Exit Sub

            Dim bReadOnly As Boolean = Not bEnabled

            For Each Pan As Control In Pans
                For Each Ctrl As Control In Pan.Controls
                    If TypeOf Ctrl Is TextBox Then

                        DirectCast(Ctrl, TextBox).ReadOnly = bReadOnly

                    ElseIf TypeOf Ctrl Is ComboBox _
                    OrElse TypeOf Ctrl Is RadioButton _
                    OrElse TypeOf Ctrl Is DateTimePicker _
                    OrElse TypeOf Ctrl Is Button _
                    OrElse TypeOf Ctrl Is NumericUpDown _
                    OrElse TypeOf Ctrl Is CheckBox Then

                        Ctrl.Enabled = bEnabled

                    ElseIf TypeOf Ctrl Is MaskedTextBox Then

                        DirectCast(Ctrl, MaskedTextBox).ReadOnly = bReadOnly

                    End If
                Next
            Next
        Catch ex As Exception
            MessageBox.Show("setControlsbEnabled" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

#Region "返回數據"


    ''' <summary>
    ''' 返回貨品資料資訊
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReturnGoodsData() As DataSet
        Dim Dset As New DataSet
        Try
            Dim Sql As String = ""
            Dim ColorSName As String = "(select Top 1 ColorSName from dbo.BSC_ColorDefine c where c.ColorCode=a.Color) as ColorSName"
            Dim SizeSName As String = "(select Top 1 SizeSName from dbo.BSC_SizeDefine c where c.SizeCode=a.Spec) as SizeSName"

            Sql &= " select Replace(BarCode,'*','') as BarCode,Code,Color,Spec,Shortname,Cnname,Enname,SupperItem,Unit," & ColorSName & "," & SizeSName & ",GoodsType,POQty from dbo.BSC_Goods   a  " & vbCrLf
            Dset = App.ReturnDataset(Sql)
        Catch ex As Exception
            MessageBox.Show("ReturnGoodsData:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Return Dset
    End Function

    ''' <summary>
    ''' 返回單號
    ''' </summary>
    ''' <param name="TableName"></param>
    ''' <param name="ColumnName"></param>
    ''' <param name="mKey"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ReturnBillNum(ByVal TableName As String, ByVal ColumnName As String, ByVal mKey As String) As String
        Dim BillNum As String = ""
        Try
            Dim Dtab As New DataTable
            Dim sql As String = ""
            sql = "Exec ReturnBillNum '" & TableName & "','" & ColumnName & "','" & mKey & "''"
            Dtab = Exec(sql, E_ExecType.DataTable)
            If Not Dtab Is Nothing AndAlso Dtab.Rows.Count > 0 Then
                BillNum = Dtab.Rows(0).Item(0) & ""
            End If
        Catch ex As Exception
            MessageBox.Show("ReturnBillNum:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK)
        End Try
        Return BillNum
    End Function

#End Region

#Region "Deliver Goods Methods"

#Region "返回送貨單Sql語句"

    ''' <summary>
    ''' 返回送單Sql語句
    ''' </summary>
    ''' <param name="WhereExp">查詢條件</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Re_DeliverGoodsSaleSql(ByVal WhereExp As String) As String

        Dim Sql As String = ""

        'Sql &= " select Convert(bit,Case Isnull(fStatus,0) when 0 then 0 else 1 End) as CheckA, a.fDeliverId,a.fSaleOrdeNo,c.fWriteNo,c.fCustomerId,c.fCustomerName,Convert(Int,Isnull(SiteNo,0)) as SiteNo, Convert(varchar(10),b.fDeliverDate,21) as fDeliverDate," & vbCrLf
        'Sql &= " fAecceptDate," & vbCrLf
        'Sql &= " fAreaId,d.AreaName,fAddressName,fTelphone,fConsigneeMan,c.fSaleDate,c.fEmployeeId,Isnull(fDSIA,0) as fDSIA,Isnull(fDSIB,0) as fDSIB,(Isnull(FTAmount,0) -Isnull(fDSIA,0) )as fBSI,Isnull(FTAmount,0) as FTAmount,fPayMode,Case IsNull(a.Ticket,0) when 1 then '是' else '否' end as  Ticket,a.fStatus,a.fSplitStatus, ''  as OutStoreHouse,a.fRemark,a.OutStoreHouse as fOutStoreHouse,FNum,BillStatus,fDStatus,c.ShopNo    " & vbCrLf
        'Sql &= " from DeliverGoodsDetail a" & vbCrLf
        'Sql &= " inner join dbo.DeliverGoodsMaster b on (a.fDeliverId=b.fDeliverId)" & vbCrLf
        'Sql &= " inner join dbo.Sal_SaleOrdeA c on (a.fSaleOrdeNo=c.fSaleOrdeNo)" & vbCrLf
        'Sql &= " left  join BSC_AreaSet d on (dbo.ReturnAreaSet_fAreaId(c.fAreaId)=d.Id) " & vbCrLf
        'Sql &= WhereExp
        'Sql &= "  order by SiteNo,d.AreaNum " & vbCrLf


        Sql &= "select Convert(bit,Case Isnull(a.fStatus,0) when 0 then 0 else 1 End) as CheckA, a.fDeliverId," & vbCrLf
        Sql &= "a.fSaleOrdeNo,c.fWriteNo,c.fCustomerId,c.fCustomerName,fAecceptDate,Convert(Int,Isnull(SiteNo,0)) as SiteNo,fAddressName,   " & vbCrLf
        Sql &= "Convert(varchar(10),b.fDeliverDate,21) as fDeliverDate, d.AreaName, " & vbCrLf
        Sql &= "  fAreaId,fTelphone,fConsigneeMan,c.fSaleDate,f.FCName as fEmployeeId," & vbCrLf
        Sql &= "Isnull(SUM(e.fDSIA),0) as fDSIA,Isnull(SUM(e.fDSIB),0) as fDSIB,(Isnull(c.FTAmount,0) -Isnull(SUM(e.fDSIA),0) )as fBSI," & vbCrLf
        Sql &= "Isnull(c.FTAmount,0) as FTAmount,fPayMode,Case IsNull(a.Ticket,0) when 1 then '是' else '否' end as  Ticket," & vbCrLf
        Sql &= "a.fStatus,a.fSplitStatus, '' as OutStoreHouse,a.fRemark,a.DeliverRemark,a.OutStoreHouse as fOutStoreHouse,a.FNum," & vbCrLf
        Sql &= "BillStatus,fDStatus,c.ShopNo from DeliverGoodsDetail a  " & vbCrLf
        'Sql &= "inner join dbo.DeliverGoodsMaster b on (a.fDeliverId=b.fDeliverId)" & vbCrLf
        'Sql &= "inner join dbo.Sal_SaleOrdeA c on (a.fSaleOrdeNo=c.fSaleOrdeNo)  " & vbCrLf
        '有些單號沒有出現在Sal_SaleOrdA中，只是作為一些備注信息存在（比如記錄一些需要返修的貨品，該客戶的信息。）
        Sql &= "inner join dbo.DeliverGoodsMaster b on (a.fDeliverId=b.fDeliverId)" & vbCrLf
        Sql &= "left join dbo.Sal_SaleOrdeA c on (a.fSaleOrdeNo=c.fSaleOrdeNo)  " & vbCrLf
        Sql &= "left  join BSC_AreaSet d on (dbo.ReturnAreaSet_fAreaId(c.fAreaId)=d.Id) " & vbCrLf
        Sql &= "left join  sal_invoiceA e  on a.fSaleOrdeNo = e.fSaleOrdeNo" & vbCrLf
        Sql &= "left join BSC_Employee f on c.fEmployeeID = f.fCode" & vbCrLf
        Sql &= WhereExp
        Sql &= "GROUP BY a.fDeliverId, a.fStatus,a.fSaleOrdeNo,c.fWriteNo,c.fCustomerID,c.fCustomerName," & vbCrLf
        Sql &= "a.SiteNo,b.fDeliverDate,a.fAecceptDate,c.fAreaID,d.AreaName,a.fAddressName,a.fTelphone," & vbCrLf
        Sql &= "a.fConsigneeMan,c.fSaleDate,c.fEmployeeID,c.fPayMode,a.Ticket,a.fSplitStatus,a.fRemark,a.DeliverRemark," & vbCrLf
        Sql &= "a.OutStoreHouse,a.FNum,a.BillStatus,a.fDStatus,c.ShopNo,d.AreaNum,c.FTAmount,f.FCName" & vbCrLf
        Sql &= "order by SiteNo,d.AreaNum " & vbCrLf


        Return Sql

    End Function

    ''' <summary>
    ''' 返回送貨單明細
    ''' </summary>
    ''' <param name="fDeliverId">送貨單號</param>
    ''' <param name="fSaleOrdeNo">銷售單號</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Re_DeliverGoodsDetailInfo(ByVal fDeliverId As String, ByVal fSaleOrdeNo As String) As String
        Dim Sql As String = ""

        Sql &= "     select a.ItemNo,Replace(b.fGoodsBarCode,'*','') as fGoodsBarCode,c.SupperItem,c.Enname,c.ShortName as GoodsName," & vbCrLf
        Sql &= "     ColorSName,SizeSName, a.fTotal as fGoodsamount,a.fSumMoney,fHouseId," & vbCrLf
        Sql &= "     Case Isnull(b.fType,0) when 1 then '換貨' +' '+Rtrim(b.fRemark) when 2 then '補件'+' '+Rtrim(b.fRemark) when 3 then '跟進'  else b.fRemark end as fRemark " & vbCrLf
        Sql &= "     from DeliverGoodsInfo a inner join Sal_SaleOrdeB b on (a.fSaleOrdeNo=b.fSaleOrdeNo and a.ItemNo=b.ItemNo)" & vbCrLf
        Sql &= "     left join BSC_Goods c on b.fGoodsBarCode=c.BarCode" & vbCrLf
        Sql &= "     inner join dbo.DeliverGoodsDetail d on a.fDeliverId=d.fDeliverId and a.fSaleOrdeNo=d.fSaleOrdeNo" & vbCrLf
        Sql &= "     left join BSC_ColorDefine e on  e.ColorCode=c.Color" & vbCrLf
        Sql &= "     left join BSC_SizeDefine f on f.SizeCode=c.Spec" & vbCrLf
        Sql &= "     where a.fDeliverId='" & fDeliverId & "' and a.fSaleOrdeNo='" & fSaleOrdeNo & "'" & vbCrLf
        Sql &= "     order by a.ItemNo " & vbCrLf & vbCrLf

        Return Sql
    End Function

#End Region

#End Region

#Region "CrystalReports Methods"

    ''' <summary>
    ''' Set CrystalReport Loginfo
    ''' </summary>
    ''' <param name="MyRepDoc">ReportDocument</param>
    ''' <remarks></remarks>
    Public Sub SetCrystalReportLoginfo(ByRef MyRepDoc As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        Try

            Dim Connectionstring As String = App.ReadConfigFile()

            Dim ServerName As String = ""
            Dim DatabaseName As String = ""
            Dim UserID As String = ""
            Dim Password As String = ""
            Dim A() As String = Connectionstring.Trim.Split(";")

            Dim Dtab As CrystalDecisions.CrystalReports.Engine.Table

            For Each TextStr As String In A
                Dim B() As String = TextStr.Trim.Split("=")
                If B.Length = 2 Then
                    Select Case B(0).Trim.ToLower
                        Case "Server".ToLower
                            ServerName = B(1).Trim
                        Case "DataBase".ToLower
                            DatabaseName = B(1).Trim
                        Case "Uid".ToLower, "User Id".ToLower
                            UserID = B(1).Trim
                        Case "Pwd".ToLower, "Password".ToLower
                            Password = B(1).Trim
                    End Select
                End If
            Next

            Dim LogInfo As New CrystalDecisions.Shared.TableLogOnInfo

            With LogInfo.ConnectionInfo
                .ServerName = ServerName
                .DatabaseName = DatabaseName
                .UserID = UserID
                .Password = Password
            End With


            For Each Dtab In MyRepDoc.Database.Tables
                Dtab.ApplyLogOnInfo(LogInfo)
            Next

            If MyRepDoc.Subreports.Count > 0 Then
                For I As Integer = 0 To MyRepDoc.Subreports.Count - 1
                    For Each Dtab In MyRepDoc.Subreports(I).Database.Tables
                        Dtab.ApplyLogOnInfo(LogInfo)
                    Next
                Next
            End If

        Catch ex As Exception
            MessageBox.Show("SetCrystalReportLoginfo:" & ex.Message & ex.StackTrace, Msg.CompanyName, MessageBoxButtons.OK)
        End Try
    End Sub

#End Region

#Region "通用報表模塊相關方法"

    Public Function formatStr(ByVal Operat As E_Operator) As String
        Dim str As String = String.Empty
        Select Case Operat
            Case E_Operator.包含
                str = " and {0} like '%{1}%' "
            Case E_Operator.從至
                str = " and {0} between '{1}' and '{2}' "
            Case E_Operator.等於
                str = " and {0} = '{1}'"
            Case E_Operator.在集合中
                str = " and {0} in (select * from str_split('{1}',','))"
            Case E_Operator.不在集合中
                str = " and {0} not (select * from str_split('{1}',',')) "
        End Select
        Return str
    End Function

    Public Function FOperator(ByVal OperatorTpye As E_Operator) As String
        Dim str As String = String.Empty
        Select Case OperatorTpye
            Case E_Operator.包含
                str = " like "
            Case E_Operator.不在集合中
                str = " not in "
            Case E_Operator.在集合中
                str = " in "
            Case E_Operator.至
                str = " <= "
            Case E_Operator.從
                str = " >= "
            Case E_Operator.等於
                str = " = "
        End Select
        Return str
    End Function

    Public Function formatvalue(ByVal Operat As E_Operator, ByVal value As String) As String
        Dim str As String = String.Empty
        Select Case Operat
            Case E_Operator.包含
                str = "%{0}%"
                str = String.Format(str, value)
            Case E_Operator.從至, E_Operator.至, E_Operator.從, E_Operator.等於, E_Operator.在集合中, E_Operator.不在集合中
                str = "{0}"
                str = String.Format(str, value)
        End Select
        Return str
    End Function

    ''' <summary>
    ''' 獲取報表中常用的顯示值的值的形式
    ''' </summary>
    ''' <param name="ValueType"></param>
    ''' <returns></returns>
    Public Function GetRptTextObjDefineContentValue(ByVal ValueType As E_ReportConstDefine) As String
        Dim strContent As String = String.Empty
        Select Case ValueType
            Case E_ReportConstDefine.操作者中文名字
                strContent = App.PubUserCode
            Case E_ReportConstDefine.操作者代號
                strContent = App.PubUserName
        End Select
        Return strContent
    End Function

    ''' <summary>
    ''' 設置報表中常用的顯示值
    ''' </summary>
    ''' <param name="ValueType"></param>
    ''' <returns></returns>
    Public Function SetRptTextObjDefineContent(ByVal ValueType As E_ReportConstDefine) As String
        Dim strContent As String = String.Empty
        Select Case ValueType
            Case E_ReportConstDefine.操作者中文名字
                strContent = "Enum." + E_ReportConstDefine.操作者中文名字.ToString
            Case E_ReportConstDefine.操作者代號
                strContent = "Enum." + E_ReportConstDefine.操作者代號.ToString
        End Select
        Return strContent
    End Function
    ''' <summary>
    ''' 獲取報表中常用的顯示格式
    ''' </summary>
    ''' <returns></returns>
    Public Function GetRptTextObjDefineContent(ByVal TextObjContent As String) As String
        Dim strContent As String = String.Empty
        If IsTextObjADefineValue(TextObjContent) Then
            strContent = TextObjContent.Substring(5)
        Else
            strContent = TextObjContent
        End If
        Return strContent
    End Function

    ''' <summary>
    ''' 判斷是否是常用的顯示值
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsTextObjADefineValue(ByVal TextObjContent As String) As Boolean
        Dim succed As Boolean = False
        If TextObjContent.StartsWith("Enum.", StringComparison.InvariantCultureIgnoreCase) Then
            succed = True
        End If
        Return succed
    End Function

    ''' <summary>
    '''根據報表參數中設置的報表TextObjContent獲取正確的形式
    ''' 對於：比如獲取 操作者中文名稱：則返回程式中的操作者的中文名
    ''' 對於其他的，則返回原來的格式
    ''' </summary>
    ''' <param name="TextObjContent"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRptTextObjContent(ByVal TextObjContent As String) As String
        If IsTextObjADefineValue(TextObjContent) Then
            Dim valType As E_ReportConstDefine
            valType = System.Enum.Parse(GetType(E_ReportConstDefine), GetRptTextObjDefineContent(TextObjContent))
            TextObjContent = GetRptTextObjDefineContentValue(valType)
        Else
            TextObjContent = TextObjContent
        End If
        Return TextObjContent
    End Function
#End Region

#Region " 客服模塊相關方法 "

    Enum CSDefineDates
        NewVersionDateAfter
        OldVersionDateBefore
    End Enum

    ''獲取客服模塊設置的時間（老版本數據在一定時間前，新版本的數據在一定時間后，用來優化查詢速度）
    Public Function GetCSDefineDate(ByVal dateType As CSDefineDates) As String
        Dim sql As String = String.Empty
        If dateType.ToString.Equals("NewVersionDateAfter", StringComparison.InvariantCultureIgnoreCase) Then
            sql += "select ListA from BSC_OtherDefineB b   where listCode='newVersionDateAfter' and" + vbNewLine
            sql += " exists(select * from BSC_OtherDefineA a where a.ItemCode='CSModeDefines' and a.ID=b.ItemNo)" + vbNewLine
        Else
            sql += "select ListA from BSC_OtherDefineB b   where listCode='OldVersionDateBefore' and" + vbNewLine
            sql += " exists(select * from BSC_OtherDefineA a where a.ItemCode='CSModeDefines' and a.ID=b.ItemNo)" + vbNewLine
        End If
        Return Exec(sql, E_ExecType.ExecuteScalar).ToString
    End Function
#End Region


    ''' <summary>
    ''' 判斷是否以及InterFace的指定方法是否已經實現
    ''' </summary>
    ''' <param name="FrmName">用戶控件名稱</param>
    ''' <param name="functionName" >函數名稱</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function isInterfaceMethodFinished(ByVal FrmName As Object, Optional ByVal functionName As String = "ActionAdd") As Boolean
        Dim iftrue As Boolean = False
        If FrmName Is Nothing Then
            Return False
        End If
        Dim m As System.Reflection.MethodInfo = FrmName.GetType().GetMethod(functionName)
        If m Is Nothing Then
            Return False
        End If
        Dim body As System.Reflection.MethodBody = m.GetMethodBody
        '本地變量是否大于1（注：函數返回值也算作一個本地變量，因此對於Function來說，方法體沒有任何代碼，也有一個本地變量）或者含有TryCatch代碼段
        If body.LocalVariables.Count > 1 Or body.ExceptionHandlingClauses.Count > 0 Then
            iftrue = True
        Else
            iftrue = False
        End If
        Return iftrue
    End Function

End Module
